package com.css.fxfzypg.modules.dataVolumeStatistics.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.manage.building.repository.entity.BuildingEntity;
import com.css.fxfzypg.manage.disasterrisk.repository.entity.DisasterRiskEntity;
import com.css.fxfzypg.manage.sampling.repository.entity.SamplingEntity;
import com.css.fxfzypg.modules.ScheduleTask.*;
import com.css.fxfzypg.modules.dataVolumeStatistics.entity.DataVolumeEntity;
import com.css.fxfzypg.modules.dataVolumeStatistics.repository.DataLumeRepository;
import com.css.fxfzypg.modules.dataVolumeStatistics.vo.HeightAndSlopeVO;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

/**
 * @author y1fan
 * @description
 */
@Repository
public class DataLumeRepositoryImpl implements DataLumeRepository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    @Override
    public Integer getDivisionNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT county) FROM data_area WHERE province ='"+province+"' and is_delete ='"+YNEnum.N+"'  ";
        }else {
            sql ="SELECT count(*) FROM data_area WHERE province ='"+province+"' and is_delete ='"+YNEnum.N+"' ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;




/*        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT DISTINCT province,city,county FROM data_area WHERE province ='"+province+"'  and  is_delete ='"+YNEnum.N+"'";
            List<HeightAndSlopeVO> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HeightAndSlopeVO.class));
            int size=0;
            for (HeightAndSlopeVO vo : list) {
                if (PlatformObjectUtils.isNotEmpty(vo.getProvince())&&PlatformObjectUtils.isEmpty(vo.getCity())&&PlatformObjectUtils.isEmpty(vo.getArea())){
                    size = this.getcountyNumByProvince(province);
                    break;
                }
                if (PlatformObjectUtils.isNotEmpty(vo.getCity())&&PlatformObjectUtils.isEmpty(vo.getArea())){
                    size+=this.getcountyNumByCity(vo.getCity());
                }
                if (PlatformObjectUtils.isNotEmpty(vo.getCity())&&PlatformObjectUtils.isNotEmpty(vo.getArea())){
                    size+=1;
                }
            }
            return size;
        }else {
            sql ="SELECT count(*) FROM data_area WHERE province ='"+province+"'  and  is_delete ='"+YNEnum.N+"'";
            int size = jdbcTemplate.queryForObject(sql, Integer.class);
            return size;
        }*/

    }

    @Override
    public List<HeightAndSlopeVO> getHeightNum(String province, String type) {
        String sql="select  province,city,area,data_length from qh_map_files where province ='"+province+"' and type like '%"+type+"%' and is_delete='0'";
       List<HeightAndSlopeVO> list= jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(HeightAndSlopeVO.class));
        return list;
    }

    @Override
    public Integer getSlopeNum(String province, String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT DISTINCT province,city,county FROM sys_slope_union WHERE province ='"+province+"'  ";
            List<HeightAndSlopeVO> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HeightAndSlopeVO.class));
            int size=0;
            for (HeightAndSlopeVO vo : list) {
                if (PlatformObjectUtils.isNotEmpty(vo.getProvince())&&PlatformObjectUtils.isEmpty(vo.getCity())&&PlatformObjectUtils.isEmpty(vo.getArea())){
                    size = this.getcountyNumByProvince(province);
                    break;
                }
                if (PlatformObjectUtils.isNotEmpty(vo.getCity())&&PlatformObjectUtils.isEmpty(vo.getArea())){
                    size+=this.getcountyNumByCity(vo.getCity());
                }
                if (PlatformObjectUtils.isNotEmpty(vo.getCity())&&PlatformObjectUtils.isNotEmpty(vo.getArea())){
                    size+=1;
                }
            }
            return size;
        }else {
            sql ="SELECT count(*) FROM sys_slope_union WHERE province ='"+province+"' ";
            int size = jdbcTemplate.queryForObject(sql, Integer.class);
            return size;
        }
    }

    @Override
    public Integer getGridNum(String province, String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT county) FROM data_pbe WHERE province ='"+province+"' and isdelete ='"+YNEnum.N+"'  ";
        }else {
            sql ="SELECT count(*) FROM data_pbe WHERE province ='"+province+"' and isdelete ='"+YNEnum.N+"' ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getRoadNum(String province, String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT county) FROM sys_road WHERE province ='"+province+"' and road_status='"+YNEnum.Y+"'  ";
        }else {
            sql ="SELECT count(*) FROM sys_road WHERE province ='"+province+"' and road_status ='"+YNEnum.Y+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getAirportNum(String province, String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT county) FROM sys_airport WHERE province ='"+province+"' and airport_status ='"+YNEnum.Y+"' ";
        }else {
            sql ="SELECT count(*) FROM sys_airport WHERE province ='"+province+"' and airport_status ='"+YNEnum.Y+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getReservoirNum(String province, String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT county) FROM data_reservoir WHERE province ='"+province+"' and isdelete ='"+YNEnum.Y+"' ";
        }else {
            sql ="SELECT count(*) FROM data_reservoir WHERE province ='"+province+"' and isdelete ='"+YNEnum.Y+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getSchoolNum(String province, String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT county) FROM data_school WHERE province ='"+province+"' and sch_isdelete ='"+YNEnum.N+"' ";
        }else {
            sql ="SELECT count(*) FROM data_school WHERE province ='"+province+"' and sch_isdelete ='"+YNEnum.N+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getHospitalNum(String province, String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT county) FROM data_hospital WHERE province ='"+province+"' and dh_isdelete ='"+YNEnum.Y+"' ";
        }else {
            sql ="SELECT count(*) FROM data_hospital WHERE province ='"+province+"' and dh_isdelete ='"+YNEnum.Y+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getDzzhNum(String province, String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT county) FROM data_geological_disasters WHERE province ='"+province+"' and dgd_isdelete ='"+YNEnum.N+"' ";
        }else {
            sql ="SELECT count(*) FROM data_geological_disasters WHERE province ='"+province+"' and dgd_isdelete ='"+YNEnum.N+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getHddzNum(String province, String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT county) FROM data_faultage WHERE province ='"+province+"' and fau_isdelete ='"+YNEnum.N+"' ";
        }else {
            sql ="SELECT count(*) FROM data_faultage WHERE province ='"+province+"' and fau_isdelete ='"+YNEnum.N+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getDangerousNum(String province, String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT county) FROM sys_riskenterprisedata WHERE province ='"+province+"' and red_status ='"+YNEnum.N+"' ";
        }else {
            sql ="SELECT count(*) FROM sys_riskenterprisedata WHERE province ='"+province+"' and red_status ='"+YNEnum.N+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getGasNum(String province, String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT county) FROM sys_gasstationdata WHERE province ='"+province+"' and gs_status ='"+YNEnum.Y+"' ";
        }else {
            sql ="SELECT count(*) FROM sys_gasstationdata WHERE province ='"+province+"' and gs_status ='"+YNEnum.Y+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getEmergencyNum(String province, String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT county) FROM sys_emergencyshelter WHERE province ='"+province+"' and es_status ='"+YNEnum.Y+"' ";
        }else {
            sql ="SELECT count(*) FROM sys_emergencyshelter WHERE province ='"+province+"' and es_status ='"+YNEnum.Y+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getRsNum(String province, String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT county) FROM sys_kangzhentu WHERE province ='"+province+"' and kzt_status ='"+YNEnum.Y+"' ";
        }else {
            sql ="SELECT count(*) FROM sys_kangzhentu WHERE province ='"+province+"' and kzt_status ='"+YNEnum.Y+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getcountyNumByCity(String city) {
        String sql="select county_num from data_city_county_num where city_name='"+city+"'";
        Integer num = jdbcTemplate.queryForObject(sql, Integer.class);
        return num;
    }

    @Override
    public Integer getcountyNumByProvince(String currProvinceName) {
        String sql="select sum(county_num) from data_city_county_num where province_name='"+currProvinceName+"'";
        Integer num = jdbcTemplate.queryForObject(sql, Integer.class);
        return num;
    }

    @Override
    public List<HeightAndSlopeVO> getDisHeightNum(String currProvinceName, String type) {
        String sql="select DISTINCT province,city,area  from qh_map_files where province ='"+currProvinceName+"' and type like '%"+type+"%' and is_delete='0'";
        List<HeightAndSlopeVO> list= jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(HeightAndSlopeVO.class));
        return list;
    }



    @Override
    public Integer getHisEarthNumByProvince(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
          sql ="select count(distinct a.name) from (select t.name ,t.geom from sys_historicalseismicdata a,district_boundary_county t where a.hsd_status='"+ YNEnum.N +"' and st_intersects(st_geomfromtext('POINT('||a.hsd_longitude||' '||a.hsd_latitude||')',4490), t.geom)) a ,district_boundary_province p WHERE st_contains(p.geom, a.geom) and p.name ='"+province+"'";
        }else {
            sql ="select count(*) from (select t.name ,t.geom from sys_historicalseismicdata a,district_boundary_county t where a.hsd_status='"+ YNEnum.N +"' and st_intersects(st_geomfromtext('POINT('||a.hsd_longitude||' '||a.hsd_latitude||')',4490), t.geom)) a ,district_boundary_province p WHERE st_contains(p.geom, a.geom) and p.name ='"+province+"'";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getDisasterNumByProvince(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
           sql ="select count(distinct a.name) from (select t.name ,t.geom from data_earthquake_disaster a,district_boundary_county t where a.ded_isdelete='"+ YNEnum.N +"' and  st_intersects(st_geomfromtext('POINT('||a.ded_longitude||' '||a.ded_latitude||')',4490), t.geom)) a ,district_boundary_province p WHERE st_contains(p.geom, a.geom) and p.name ='"+province+"'";
        }else {
            sql ="select count(*) from (select t.name ,t.geom from data_earthquake_disaster a,district_boundary_county t where a.ded_isdelete='"+ YNEnum.N +"' and  st_intersects(st_geomfromtext('POINT('||a.ded_longitude||' '||a.ded_latitude||')',4490), t.geom)) a ,district_boundary_province p WHERE st_contains(p.geom, a.geom) and p.name ='"+province+"'";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getPreAppraisalNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="select count(distinct a.name) from (select t.name ,t.geom from data_his_pr a,district_boundary_county t where a.del_flag='0' and st_intersects(st_geomfromtext('POINT('||a.dhp_epicenter_longitude||' '||a.dhp_epicenter_latitude||')',4490), t.geom)) a ,district_boundary_province p WHERE st_contains(p.geom, a.geom) and p.name ='"+province+"' ";
        }else {
           sql ="select count(*) from (select t.name ,t.geom from data_his_pr a,district_boundary_county t where a.del_flag='0' and st_intersects(st_geomfromtext('POINT('||a.dhp_epicenter_longitude||' '||a.dhp_epicenter_latitude||')',4490), t.geom)) a ,district_boundary_province p WHERE st_contains(p.geom, a.geom) and p.name ='"+province+"' ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getDistrictInNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT prefecture ) FROM sys_countiescondition where province ='"+province+"' and is_delete ='"+YNEnum.N+"' ";
        }else {
           sql ="SELECT count(*) FROM sys_countiescondition where province ='"+province+"' and is_delete ='"+YNEnum.N+"' ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;

    }

    @Override
    public Integer getPopulationCensusNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT prefecture ) FROM data_peo where province ='"+province+"' and is_delete ='"+YNEnum.N+"' and (peo_total is not null or peo_avg_house is not null  or peo_avg_area is not null) ";
        }else {
            sql ="SELECT count(*) FROM data_peo where province ='"+province+"' and is_delete ='"+YNEnum.N+"' and (peo_total is not null or peo_avg_house is not null  or peo_avg_area is not null) ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getBuildingNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT prefecture ) FROM sys_buildingvulnerability_new where province ='"+province+"'   ";
        }else {
            sql ="SELECT count(*) FROM sys_buildingvulnerability_new where province ='"+province+"'   ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getHouseNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT prefecture ) FROM data_house where province ='"+province+"' and is_delete='"+YNEnum.N+"' and (high_house_price is not null or steel_price is not null or concrete_price is not null or brickhun_price is not null or brick_price is not null or soil_price is not null or wood_price is not null or stone_price is not null or other_price is not null )  ";
        }else {
            sql ="SELECT count(*) FROM data_house where province ='"+province+"' and is_delete='"+YNEnum.N+"'  and (high_house_price is not null or steel_price is not null or concrete_price is not null or brickhun_price is not null or brick_price is not null or soil_price is not null or wood_price is not null or stone_price is not null or other_price is not null )  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getProjectNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT dkp_prefecture ) FROM data_key_project where dkp_province ='"+province+"' and dkp_is_delete='"+YNEnum.N+"' ";
        }else {
            sql ="SELECT count(*) FROM data_key_project where dkp_province ='"+province+"' and dkp_is_delete='"+YNEnum.N+"' ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getGdpNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT prefecture ) FROM data_gdp where  province ='"+province+"' and is_delete='"+YNEnum.N+"'   and ( gdp is not null or peo_avg_gdp is not null) ";
        }else {
            sql ="SELECT count(*) FROM data_gdp where province ='"+province+"' and is_delete='"+YNEnum.N+"' and ( gdp is not null or peo_avg_gdp is not null) ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getContingencyNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
           sql ="SELECT count(DISTINCT prefecture ) FROM sys_sc_seismicsetout  where province ='"+province+"' and is_delete='"+YNEnum.N+"' ";
        }else {
           sql ="SELECT count(*) FROM sys_sc_seismicsetout  where province ='"+province+"' and is_delete='"+YNEnum.N+"' ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getBuildingsUnitNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT county ) FROM ypg_building_single  where province ='"+province+"' and del_flag='"+YNEnum.N+"'  ";
        }else {
            sql ="SELECT count(*) FROM ypg_building_single  where province ='"+province+"' and del_flag='"+YNEnum.N+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getSurveyPointNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT area ) FROM ypg_sampling_survey_new where province ='"+province+"' and del_flag='"+YNEnum.N+"'  ";
        }else {
            sql ="SELECT count(*) FROM ypg_sampling_survey_new where province ='"+province+"' and del_flag='"+YNEnum.N+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getRiskPointNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT county ) FROM ypg_disaster_risk where province ='"+province+"' and del_flag='"+YNEnum.N+"' ";
        }else {
            sql ="SELECT count(*) FROM ypg_disaster_risk where province ='"+province+"' and del_flag='"+YNEnum.N+"' ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getfatalNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT area ) FROM ypg_area_lethality_difference where province ='"+province+"' and del_flag='"+YNEnum.N+"' ";
        }else {
            sql ="SELECT count(*) FROM ypg_area_lethality_difference where province ='"+province+"' and del_flag='"+YNEnum.N+"' ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getTownshipInNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT prefecture ) FROM sys_village_town_condition where province ='"+province+"' and is_delete='"+YNEnum.N+"'  ";
        }else {
            sql ="SELECT count(*) FROM sys_village_town_condition where province ='"+province+"' and is_delete='"+YNEnum.N+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getDistrictPoNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT prefecture ) FROM data_hos_peo where  province ='"+province+"' and is_delete='"+YNEnum.N+"'  ";
        }else {
            sql ="SELECT count(*) FROM data_hos_peo where  province ='"+province+"' and is_delete='"+YNEnum.N+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public Integer getTownshipPoNum(String province,String toString) {
        String sql="";
        if (toString.equals(YNEnum.N.toString())){
            sql ="SELECT count(DISTINCT prefecture ) FROM data_village_town_hos_peo where  province ='"+province+"' and is_delete='"+YNEnum.N+"'  ";
        }else {
            sql ="SELECT count(*) FROM data_village_town_hos_peo where  province ='"+province+"' and is_delete='"+YNEnum.N+"'  ";
        }
        int size = jdbcTemplate.queryForObject(sql, Integer.class);
        return size;
    }

    @Override
    public void save(List<DataVolumeEntity> entities) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(entities.toArray());
        String sql = "INSERT INTO  ypg_data_volume  " +
                "(id,province,county_num,division_num,division_proportion,height_num,height_proportion," +
                "slope_num,slope_proportion,grid_num,grid_proportion,road_num,road_proportion,airport_num,airport_proportion," +
                "reservoir_num,reservoir_proportion,school_num,school_proportion,hospital_num,hospital_proportion,dzzh_num,dzzh_proportion," +
                "hddc_num,hddc_proportion,dangerous_num,dangerous_proportion,gas_num," +
                "gas_proportion,emergency_num,emergency_proportion,rs_num," +
                "rs_proportion,information_num,information_proportion,disaster_num," +
                "disaster_proportion,pre_appraisal_num,pre_appraisal_proportion," +
                "district_in_num,district_in_proportion,township_in_num,township_in_proportion," +
                "district_po_num,district_po_proportion,township_po_num,township_po_proportion," +
                "population_census_num,population_census_proportion," +
                "building_num,building_proportion,house_num,house_proportion," +
                "project_num,project_proportion,gdp_num,gdp_proportion,contingency_num,contingency_proportion," +
                "buildings_unit_num," +
                "buildings_unit_proportion,survey_point_num,survey_point_proportion,risk_point_num,risk_point_proportion,fatal_num,fatal_proportion,code) " +
                "values" +
                "(:id,:province,:countyNum,:divisionNum,:divisionProportion,:heightNum,:heightProportion,:slopeNum,:slopeProportion,:gridNum,:gridProportion,:roadNum,:roadProportion," +
                ":airportNum,:airportProportion,:reservoirNum,:reservoirProportion,:schoolNum,:schoolProportion,:hospitalNum,:hospitalProportion,:dzzhNum,:dzzhProportion," +
                ":hddcNum,:hddcProportion,:dangerousNum,:dangerousProportion,:gasNum,:gasProportion,:emergencyNum,:emergencyProportion,:rsNum," +
                ":rsProportion,:informationNum,:informationProportion,:disasterNum," +
                ":disasterProportion,:preAppraisalNum,:preAppraisalProportion," +
                ":districtInNum,:districtInProportion,:townshipInNum,:townshipInProportion," +
                ":districtPoNum,:districtPoProportion,:townshipPoNum,:townshipPoProportion," +
                ":populationCensusNum,:populationCensusProportion," +
                ":buildingNum,:buildingProportion,:houseNum,:houseProportion," +
                ":projectNum,:projectProportion,:gdpNum,:gdpProportion,:contingencyNum,:contingencyProportion," +
                ":buildingsUnitNum,:buildingsUnitProportion,:surveyPointNum,:surveyPointProportion,:riskPointNum,:riskPointProportion,:fatalNum,:fatalProportion,:code)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public void delete() {
        String sql ="delete from ypg_data_volume";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<DataVolumeEntity> getData() {
        String sql="select distinct province,county_num,division_num,division_proportion,height_num,height_proportion,slope_num,slope_proportion,grid_num,grid_proportion,road_num,road_proportion,airport_num,airport_proportion, reservoir_num,reservoir_proportion,school_num,school_proportion,hospital_num,hospital_proportion,dzzh_num,dzzh_proportion,hddc_num,hddc_proportion,dangerous_num,dangerous_proportion,gas_num, gas_proportion,emergency_num,emergency_proportion,rs_num,rs_proportion,information_num,information_proportion,disaster_num, disaster_proportion,pre_appraisal_num,pre_appraisal_proportion, district_in_num,district_in_proportion,township_in_num,township_in_proportion, district_po_num,district_po_proportion,township_po_num,township_po_proportion, population_census_num,population_census_proportion, building_num,building_proportion,house_num,house_proportion, project_num,project_proportion,gdp_num,gdp_proportion,contingency_num,contingency_proportion, buildings_unit_num, buildings_unit_proportion,survey_point_num,survey_point_proportion,risk_point_num,risk_point_proportion,fatal_num,fatal_proportion,code  from ypg_data_volume order by code ";
        List<DataVolumeEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DataVolumeEntity.class));
        return list;
    }

    @Override
    public List<SamplingEntity> coverage() {
        String sql ="select * from ypg_sampling_survey_new where del_flag='0'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(SamplingEntity.class));
    }

    @Override
    public String getProvinceCodeByProvinceId(String province) {
        String sql="select province_code from data_city_county_num where province_name='"+province+"'";
        List<String> list = jdbcTemplate.queryForList(sql, String.class);
        return list.get(0);
    }

    @Override
    public void saveCoverage(List<CoverageEntity> coverageList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(coverageList.toArray());
        String sql = "INSERT INTO ypg_province_coverage (id,county_coverage,province,village_coverage,code) values(:id,:countyCoverage,:province,:villageCoverage,:code)";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public Integer getCountyNum() {
        String sql="SELECT sum(county_num) FROM data_city_county_num";
        Integer num = jdbcTemplate.queryForObject(sql, Integer.class);
        return num;
    }

    @Override
    public Integer getProvinceHouseNumByProvinceName(String province) {
        String sql="select count(*) from ypg_building_single where province='"+province+"' and  del_flag='0' ";
        Integer num = jdbcTemplate.queryForObject(sql, Integer.class);
        return num;
    }

    @Override
    public List<BuildingEntity> getAllBuilding() {
        String sql="SELECT * from ypg_building_single  where del_flag='0'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(BuildingEntity.class));
    }

    @Override
    public void saveHouseNum(List<YpgProHouseNumCount> resultList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(resultList.toArray());
        namedParameterJdbcTemplate.batchUpdate("INSERT INTO ypg_pro_house_num_count(id,province,house_num,code,rate values (:id,:province,:houseNum,:code,:rate,)", beanSources);
    }

    @Override
    public List<YpgHouseTypeCount> getHouseType() {
        String sql="select building_structure as structure_type,count(0)*100/(select count(1) from ypg_building_single WHERE del_flag='0') as structure_type_proportion  from ypg_building_single WHERE del_flag='0' GROUP BY building_structure";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(YpgHouseTypeCount.class));
    }

    @Override
    public List<YpgHouseTypeCount> getHouseTypeByCityName(String city) {
        String sql="select building_structure as structure_type,count(0)*100/(select count(1) from ypg_building_single WHERE del_flag='0'and city='"+city+"') as structure_type_proportion  from ypg_building_single WHERE del_flag='0' and city='"+city+"'GROUP BY building_structure";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(YpgHouseTypeCount.class));
    }

    @Override
    public void saveHouseType(List<YpgHouseTypeCount> typeCounts) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(typeCounts.toArray());
        namedParameterJdbcTemplate.batchUpdate("INSERT INTO ypg_house_type_count(id,structure_type,structure_type_proportion,code values (:id,:structureType,:structureTypeProportion,:code)", beanSources);

    }

    @Override
    public void saveHouseYear(List<YpgBuildMonomerCount> monomerCounts) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(monomerCounts.toArray());
        namedParameterJdbcTemplate.batchUpdate("INSERT INTO ypg_build_monomer_count(id,years,build_monomer_num,code values (:id,:years,:buildMonomerNum,:code)", beanSources);

    }

    @Override
    public List<DisasterRiskEntity> getDisRisk() {
        String sql="SELECT * from ypg_disaster_risk  where del_flag='0'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(DisasterRiskEntity.class));
    }

    @Override
    public void saveRiskNum(List<YpgProDamagePointCount> pointCountList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(pointCountList.toArray());
        namedParameterJdbcTemplate.batchUpdate("INSERT INTO ypg_pro_damage_point_count(id,province,damage_point_num,code values (:id,:province,:damagePointNum,:code)", beanSources);

    }

    @Override
    public void saveRiskType(List<YpgDamagePointTypeCount> typeCountList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(typeCountList.toArray());
        namedParameterJdbcTemplate.batchUpdate("INSERT INTO ypg_damage_point_type_count(id,damage_point_type,damage_point_proportion,code values (:id,:damagePointType,:damagePointProportion,:code)", beanSources);

    }

    @Override
    public void saveRiskYear(List<YpgDamagePointSituationCount> yearList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(yearList.toArray());
        namedParameterJdbcTemplate.batchUpdate("INSERT INTO ypg_damage_point_years(id,years,damage_point_num,code values (:id,:years,:damagePointNum,:code)", beanSources);

    }

    @Override
    public void savePointCounts(List<YpgProSurveyPointCount> surveyPointCounts) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(surveyPointCounts.toArray());
        namedParameterJdbcTemplate.batchUpdate("INSERT INTO ypg_pro_survey_point_count(id,province,survey_point_num,code values (:id,:province,:surveyPointNum,:code)", beanSources);
    }

    @Override
    public void delCoverage() {
        String sql = "delete from ypg_province_coverage";
        jdbcTemplate.update(sql);
    }

    @Override
    public void delPointCounts() {
        String sql = "delete from ypg_pro_survey_point_count";
        jdbcTemplate.update(sql);
    }

    @Override
    public void delHouseNum() {
        String sql = "delete from ypg_pro_house_num_count";
        jdbcTemplate.update(sql);
    }

    @Override
    public void delHouseType() {
        String sql = "delete from ypg_house_type_count";
        jdbcTemplate.update(sql);
    }

    @Override
    public void delHouseYear() {
        String sql = "delete from ypg_build_monomer_count";
        jdbcTemplate.update(sql);
    }

    @Override
    public void delRiskNum() {
        String sql = "delete from ypg_pro_damage_point_count";
        jdbcTemplate.update(sql);
    }

    @Override
    public void delRiskType() {
        String sql = "delete from ypg_damage_point_type_count";
        jdbcTemplate.update(sql);
    }

    @Override
    public void delRiskYear() {
        String sql = "delete from ypg_damage_point_years";
        jdbcTemplate.update(sql);
    }


}
